﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spWH_Log]
#-- Purpose:		Copies the config from the Warehouse
#--	Last Update:	05/09/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spWH_Config]
AS

--- Declare Local Variables
DECLARE	@sSQL varchar(MAX)

IF EXISTS (SELECT TOP 1 * FROM sys.servers WHERE name = '$(MSXServer)')
  BEGIN
	SELECT	@sSQL = '--- Declare Local Variables
DECLARE @mnt_server_ssd_id int, @RAM_pre int, @RAM_post int, @count_pre int, @count_post int

--- Determine the ssd_id of the current server
SELECT	@mnt_server_ssd_id = [ssd_id]
FROM	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
WHERE	[ssd_sqlserver_name_ro] = @@SERVERNAME

IF @mnt_server_ssd_id IS NOT NULL
  BEGIN
	--- Get the count of records (determine exist - yes/no?)
	SELECT		@count_pre = COUNT(*) FROM [dbo].[tblMaint_Config]
	SELECT		@count_post = COUNT(*) FROM [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Config] WHERE [cfg_ssd_id] = @mnt_server_ssd_id

	--- Record does not exist at MSX, but does local.  Delete it.
	IF @count_post = 0 AND @count_pre > 0
	  BEGIN
		DELETE FROM [dbo].[tblMaint_Config]
	  END

	--- Record exists at MSX, but not local.  Add it.
	ELSE IF @count_post > 0 AND @count_pre = 0
	  BEGIN
		INSERT INTO [dbo].[tblMaint_Config]
		(
			[cfg_recover_window_days],
			[cfg_bkp_skip_db],
			[cfg_maint_skip_db],
			[cfg_maint_nomove_ndx],
			[cfg_bkp_prescript],
			[cfg_bkp_postscript],
			[cfg_local_domain],
			[cfg_max_server_RAM]
		)
		SELECT		[cfg_recover_window_days],
					[cfg_bkp_skip_db],
					[cfg_maint_skip_db],
					[cfg_maint_nomove_ndx],
					[cfg_bkp_prescript],
					[cfg_bkp_postscript],
					[cfg_local_domain],
					[cfg_max_server_RAM]
		FROM		[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Config]
		WHERE		[cfg_ssd_id] = @mnt_server_ssd_id
	  END

	--- Determine if the MSX and local records are the same.  If not make local same as MSX
	ELSE IF EXISTS (
			SELECT		[cfg_recover_window_days],
						[cfg_bkp_skip_db],
						[cfg_maint_skip_db],
						[cfg_maint_nomove_ndx],
						[cfg_bkp_prescript],
						[cfg_bkp_postscript],
						[cfg_local_domain],
						[cfg_max_server_RAM]
			FROM		[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Config]
			WHERE		[cfg_ssd_id] = @mnt_server_ssd_id

			EXCEPT

			SELECT		[cfg_recover_window_days],
						[cfg_bkp_skip_db],
						[cfg_maint_skip_db],
						[cfg_maint_nomove_ndx],
						[cfg_bkp_prescript],
						[cfg_bkp_postscript],
						[cfg_local_domain],
						[cfg_max_server_RAM]
			FROM		[dbo].[tblMaint_Config]
		)
		OR EXISTS (
			SELECT		[cfg_recover_window_days],
						[cfg_bkp_skip_db],
						[cfg_maint_skip_db],
						[cfg_maint_nomove_ndx],
						[cfg_bkp_prescript],
						[cfg_bkp_postscript],
						[cfg_local_domain],
						[cfg_max_server_RAM]
			FROM		[dbo].[tblMaint_Config]

			EXCEPT

			SELECT		[cfg_recover_window_days],
						[cfg_bkp_skip_db],
						[cfg_maint_skip_db],
						[cfg_maint_nomove_ndx],
						[cfg_bkp_prescript],
						[cfg_bkp_postscript],
						[cfg_local_domain],
						[cfg_max_server_RAM]
			FROM		[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Config]
			WHERE		[cfg_ssd_id] = @mnt_server_ssd_id
		)
		BEGIN
			SELECT		@RAM_pre = [cfg_max_server_RAM]
			FROM		[dbo].[tblMaint_Config]

			SELECT		@RAM_post = [cfg_max_server_RAM]
			FROM		[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Config]
			WHERE		[cfg_ssd_id] = @mnt_server_ssd_id

			UPDATE		[dbo].[tblMaint_Config]
			SET			[cfg_recover_window_days] = t1.[cfg_recover_window_days],
						[cfg_bkp_skip_db] = t1.[cfg_bkp_skip_db],
						[cfg_maint_skip_db] = t1.[cfg_maint_skip_db],
						[cfg_maint_nomove_ndx] = t1.[cfg_maint_nomove_ndx],
						[cfg_bkp_prescript] = t1.[cfg_bkp_prescript],
						[cfg_bkp_postscript] = t1.[cfg_bkp_postscript],
						[cfg_local_domain] = t1.[cfg_local_domain],
						[cfg_max_server_RAM] = t1.[cfg_max_server_RAM]
			FROM		[dbo].[tblMaint_Config]
			CROSS JOIN	(
							SELECT		[cfg_recover_window_days],
										[cfg_bkp_skip_db],
										[cfg_maint_skip_db],
										[cfg_maint_nomove_ndx],
										[cfg_bkp_prescript],
										[cfg_bkp_postscript],
										[cfg_local_domain],
										[cfg_max_server_RAM]
							FROM		[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Config]
							WHERE		[cfg_ssd_id] = @mnt_server_ssd_id
						) t1

			--- If the RAM Changed, reset the configured instance RAM
			IF @RAM_pre <> @RAM_post
				EXEC [dbo].[spMaint_InitRAM]
		END
  END
ELSE
	RAISERROR(''This server has not been registered with the MSX Server'', 16, 1)'

	EXEC	(@sSQL)
  END